﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using DTO;
namespace DAO
{
    public class NguoiDungDAO
    {

        //----------------------------------------------------------------------------------------------------------------------------------
        //KIỂM TRA TÀI KHOẢN CHO ĐĂNG NHẬP
        public static int KiemTraDangNhap(string username, string password)
        {
            SqlConnection cn;
            cn = DataProvider.ConnectionData();

            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "SP_CHECK_ACCOUNT_LOGIN";
            cmd.Connection = cn;

            SqlParameter para;
            para = new SqlParameter("USERNAME", SqlDbType.NVarChar, 20);
            para.Direction = ParameterDirection.Input;
            para.Value = username;
            cmd.Parameters.Add(para);

            para = new SqlParameter("PASSWORD", SqlDbType.NVarChar, 10);
            para.Direction = ParameterDirection.Input;
            para.Value = password;
            cmd.Parameters.Add(para);

            para = new SqlParameter("KQ", SqlDbType.Int);
            para.Direction = ParameterDirection.Output;
            cmd.Parameters.Add(para);

            cmd.ExecuteNonQuery();
            int KQ = (int)cmd.Parameters["KQ"].Value;
            cn.Close();

            return KQ;
        }

        //lấy MALOAIND CỦA USER ĐĂNG NHẬP
        public static int LAY_MALOAIND(string username, string password)
        {
            SqlConnection cn;
            cn = DataProvider.ConnectionData();

            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "SP_LAY_MALOAIND";
            cmd.Connection = cn;

            SqlParameter para;
            para = new SqlParameter("USERNAME", SqlDbType.NVarChar, 20);
            para.Direction = ParameterDirection.Input;
            para.Value = username;
            cmd.Parameters.Add(para);

            para = new SqlParameter("PASSWORD", SqlDbType.NVarChar, 10);
            para.Direction = ParameterDirection.Input;
            para.Value = password;
            cmd.Parameters.Add(para);

            para = new SqlParameter("MALOAIND", SqlDbType.Int);
            para.Direction = ParameterDirection.Output;
            cmd.Parameters.Add(para);

            cmd.ExecuteNonQuery();
            int MALOAIND = (int)cmd.Parameters["MALOAIND"].Value;
            cn.Close();

            return MALOAIND;
        }
        //------------------------------------------------------------------------------------------------------------------------------------

        //kiểm tra email xác nhận khi kh quên mật khẩu

        public static int KiemTraEmail(string email)
        {
            SqlConnection cn;
            cn = DataProvider.ConnectionData();

            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "SP_CHECK_EMAIL";
            cmd.Connection = cn;

            SqlParameter para;
            para = new SqlParameter("EMAIL", SqlDbType.NVarChar, 20);
            para.Direction = ParameterDirection.Input;
            para.Value = email;
            cmd.Parameters.Add(para);

            para = new SqlParameter("KQ", SqlDbType.Int);
            para.Direction = ParameterDirection.Output;
            cmd.Parameters.Add(para);

            cmd.ExecuteNonQuery();
            int KQ = (int)cmd.Parameters["KQ"].Value;
            cn.Close();

            return KQ;
        }

        //------------------------------------------------------------------------------------------------------------------------------------

        //KIỂM TRA TÀI KHOẢN ĐÃ TỒN TẠI HAY CHƯA CHO ĐĂNG KÝ
        public static int KiemTraTaiKhoan(NguoiDung user)
        {
            SqlConnection cn;
            cn = DataProvider.ConnectionData();

            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "SP_KIEMTRATAIKHOAN";
            cmd.Connection = cn;

            SqlParameter para;
            para = new SqlParameter("USERNAME", SqlDbType.NVarChar, 20);
            para.Direction = ParameterDirection.Input;
            para.Value = user.Username;
            cmd.Parameters.Add(para);


            para = new SqlParameter("KQ", SqlDbType.Int);
            para.Direction = ParameterDirection.Output;
            cmd.Parameters.Add(para);

            cmd.ExecuteNonQuery();
            int KQ = (int)cmd.Parameters["KQ"].Value;
            cn.Close();

            return KQ;
        }

        //Thêm thông tin đăng ký xuống bảng NguoiDung và lấy MaUser
        public static string Insert_NguoiDung_Lay_MaUser(NguoiDung user)
        {
            SqlConnection cn;
            cn = DataProvider.ConnectionData();

            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "SP_INSERT_NGUOIDUNG_LAY_MAUSER";
            cmd.Connection = cn;

            SqlParameter para;
            para = new SqlParameter("USERNAME", SqlDbType.NVarChar, 100);
            para.Direction = ParameterDirection.Input;
            para.Value = user.Username;
            cmd.Parameters.Add(para);

            para = new SqlParameter("PASSWORD", SqlDbType.NVarChar, 100);
            para.Direction = ParameterDirection.Input;
            para.Value = user.Password;
            cmd.Parameters.Add(para);

            para = new SqlParameter("MAUSER", SqlDbType.NVarChar, 100);
            para.Direction = ParameterDirection.Output;
            cmd.Parameters.Add(para);

            cmd.ExecuteNonQuery();
            string MaUser = cmd.Parameters["MAUSER"].Value.ToString();
            cn.Close();
            return MaUser;

        }
        public static int ThemKhachHang(NguoiDung user) //hàm thêm sẽ trả về giá trị 0 cho thêm thành công và 1 cho không thành công
        {

            SqlConnection cn;
            cn = DataProvider.ConnectionData();

            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "SP_INSERT_KHACHHANG";
            cmd.Connection = cn;

            SqlParameter para;
            //para = new SqlParameter("USERNAME", SqlDbType.NVarChar, 100);
            //para.Direction = ParameterDirection.Input;
            //para.Value = user.Username;
            //cmd.Parameters.Add(para);


            //para = new SqlParameter("PASSWORD", SqlDbType.VarChar, 100);
            //para.Direction = ParameterDirection.Input;
            //para.Value = user.Password;
            //cmd.Parameters.Add(para);

            para = new SqlParameter("MALOAIKH", SqlDbType.Int);
            para.Direction = ParameterDirection.Input;
            para.Value = user.MaLoaiKH;
            cmd.Parameters.Add(para);

            para = new SqlParameter("HOTEN", SqlDbType.NVarChar, 100);
            para.Direction = ParameterDirection.Input;
            para.Value = user.HoTen;
            cmd.Parameters.Add(para);


            //LẤY MAUSER TỪ BẢNG NGƯỜI DÙNG ĐỂ TRUYỀN VÀO KHÁCH HÀNG
            string MAUSER = Insert_NguoiDung_Lay_MaUser(user);

            para = new SqlParameter("MAUSER", SqlDbType.NVarChar, 100);
            para.Direction = ParameterDirection.Input;
            //para.Value = user.MaUser;
            para.Value = MAUSER;
            cmd.Parameters.Add(para);

            para = new SqlParameter("NGAYSINH", SqlDbType.Date);
            para.Direction = ParameterDirection.Input;
            para.Value = user.NgaySinh;
            cmd.Parameters.Add(para);

            para = new SqlParameter("EMAIL", SqlDbType.NVarChar, 100);
            para.Direction = ParameterDirection.Input;
            para.Value = user.Email;
            cmd.Parameters.Add(para);


            para = new SqlParameter("KQ", SqlDbType.Int);
            para.Direction = ParameterDirection.Output;
            cmd.Parameters.Add(para);


            cmd.ExecuteNonQuery();
            int KQ = (int)cmd.Parameters["KQ"].Value;
            cn.Close();
            return KQ;
        }

        //lấy password khi người dùng quên mật khẩu
        public static string Lay_Passwors(string email)
        {
            SqlConnection cn = DataProvider.ConnectionData();
            string function = "Select dbo.FUNC_LAY_PASSWORD ( @EMAIL )";
            SqlCommand cmd = new SqlCommand(function, cn);
            cmd.Parameters.AddWithValue("@EMAIL", email);
            cmd.CommandType = CommandType.Text;
            string pass = cmd.ExecuteScalar().ToString();
            cn.Close();
            return pass;
        }
    }
}
